SQL Date Functions

    SQL provides a specific data type for date-related data; there may come many cases when we require some special functions for all those dates stored in the database. For example, if we want to show the current date and time, then we can use the date NOW() Function. Different RDBMS have different date functions to perform a specific date operation, here in this article all the SQL date-related functions we have provided are from MYSQL. Some of the functions may not work on all the other RDBMS such as Oracle, SQL Server, PostgreSQL, etc.

    SQL Date Functions

    • ADDDATE()
    • ADDTIME()
    • CONVERT_TZ()
    • CURDATE()
    • CURRENT_DATE(), CURRENT_DATE
    • CURRENT_TIME(), CURRENT_TIME
    • CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
    • CURTIME()
    • DATE_FORMAT()
    • DATE()
    • DATEDIFF()
    • DAY()
    • DAYNAME()
    • DAYOFMONTH()
    • DAYOFWEEK()
    • DAYOFYEAR()
    • EXTRACT
    • FROM_DAYS()
    • FROM_UNIXTIME()
    • HOUR()
    • LAST_DAY
    • LOCALTIME(), LOCALTIME
    • LOCALTIMESTAMP, LOCALTIMESTAMP()
    • MAKEDATE()
    • MAKETIME
    • MICROSECOND()
    • MINUTE()
    • MONTH()
    • MONTHNAME()
    • NOW()
    • PERIOD_ADD()
    • PERIOD_DIFF()
    • QUARTER()
    • SEC_TO_TIME()
    • SECOND()
    • STR_TO_DATE()
    • SUBDATE()
    • SUBTIME()
    • SYSDATE()
    • TIME_FORMAT()
    • TIME_TO_SEC()
    • TIME()
    • TIMEDIFF()
    • TIMESTAMP()
    • TIMESTAMPADD()
    • TIMESTAMPDIFF()
    • TO_DAYS()
    • UNIX_TIMESTAMP(
    • UTC_DATE()
    • UTC_TIME()
    • UTC_TIMESTAMP()
    • WEEK()
    • WEEKDAY()
    • WEEKOFYEAR()
    • YEAR()
    • YEARWEEK()

    1. ADDDATE()

    ADDDATE() Function is used to add interval days on a date. It accepts two values as parameters. The first parameter should be the date on which we are supposed to add Days. The second parameter should be the INTERVAL keyword with the number of unit values we want to add on the date. Syntax 

    ADDDATE(date,INTERVAL unit Value);

    Example 

    SELECT DATE_ADD('2020-03-03', INTERVAL 31 DAY);

    Output 

    +-----------------------------------------+
| DATE_ADD('2020-03-03', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2020-04-03                              |
+-----------------------------------------+

    1 row in set (0.05 sec) Unit Values to add Different intervals:

    • MICROSECOND
    • SECOND
    • MINUTE
    • HOUR
    • DAY
    • WEEK
    • MONTH
    • QUARTER
    • YEAR
    • SECOND_MICROSECOND
    • MINUTE_MICROSECOND
    • MINUTE_SECOND
    • HOUR_MICROSECOND
    • HOUR_SECOND
    • HOUR_MINUTE
    • DAY_MICROSECOND
    • DAY_SECOND
    • DAY_MINUTE
    • DAY_HOUR
    • YEAR_MONTH

    2. ADDTIME()

    The Function of ADDTIME() is similar to ADDATE, but here instead of adding Dates, we add time. Syntax: 

    ADDTIME(date&time, time to add)

    Example: 

    SELECT ADDTIME('2020-01-31 23:59:59.999999','1 1:1:1.000002');

Output
+--------------------------------------------------------+
| ADDTIME('2020-01-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2020-02-02 01:01:01.000001                             |
+--------------------------------------------------------+ 

    3. CONVERT_TZ()

    This function is used to convert the time zone from one to another. Syntax 

    CONVERT_TZ(date,from,to)

    Example 

    SELECT CONVERT_TZ('2020-01-01 12:00:00','+00:00','+10:00');

Output
+-----------------------------------------------------+
| CONVERT_TZ('2020-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2020-01-01 22:00:00                                 |
+-----------------------------------------------------+

    4. CURDATE()

    This function returns the current cate in “YYYY-MM-DD” format. Example 

    SELECT CURDATE() as Today;

Output
+------------+
| Today     |
+------------+
| 2020-05-15 |
+------------+

    5. CURRENT_DATE & CURRENT_DATE()

    These two are similar to CURDATE(). Example 

    SELECT CURRENT_DATE;

Output
+--------------+
| CURRENT_DATE |
+--------------+
| 2020-05-15   |
+--------------+

    6. CURTIME()

    This function returns the current time in HH:MM: SS format. 

    SELECT CURTIME();

Output
+-----------+
| CURTIME() |
+-----------+
| 12:07:56  |
+-----------+

    7. CURRENT_TIME & CURRENT_TIME()

    These are similar to CURTIME() function Example 

    SELECT CURRENT_TIME;

Output
+--------------+
| CURRENT_TIME |
+--------------+
| 12:08:49     |
+--------------+

    8. DATE()

    With the Date() function, we can extract the date from a DateTime expression. Example 

    SELECT DATE('2019-11-02 02:03:04');

Output
+-----------------------------+
| DATE('2019-11-02 02:03:04') |
+-----------------------------+
| 2019-11-02                  |
+-----------------------------+

    9. DATEDIFF()

    This Function can return the number of days difference between two date or DateTime periods. Example: 

    SELECT DATEDIFF('2010-01-31 23:59:59','2000-01-31');

Output
+----------------------------------------------+
| DATEDIFF('2010-01-31 23:59:59','2000-01-31') |
+----------------------------------------------+
|                                         3653 |
+----------------------------------------------+

    10. DATE_FORMAT

    This command is used to format a date and time into different string formats, so for a user, it becomes easy to read the date and time. Syntax 

     DATE_FORMAT(date,String_format)

    Example 

    SELECT DATE_FORMAT('2020-01-01 22:23:00', '%W %M %Y');

Output
+------------------------------------------------+
| DATE_FORMAT('2020-01-01 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Wednesday January 2020                         |
+------------------------------------------------+

    To format the date, we can use various string Specifiers such as:

    String Specifiers Description
    %a It specifies the abbreviate Weekday names such as Sun, Mon…Sat
    %b It specifies the abbreviate Month names such as Jan, Feb…Dec
    %c Numeric form for months 0 to 12
    %D Specify the day of the month, such as 0 th , 1 st , 2 nd , …. 31 st .
    %d Numeric representation of month days 0 to 31
    %e Similar to %d
    %f Specify microseconds 000000 to 999999
    %H Specify hours from 00 to 23
    %h Specify hours from 01 to 12
    %I Similar to %h specifies hours from 01 to 12 with AM and PM domain
    %i Show minutes in numeric 00 to 59
    %j Show year days 001 to 366
    %k Show hour from 0 to 23
    %M Specify month name from January to December
    %m Specify months in numeric from 00 to 12
    %p Specify Time-domain AM and PM
    %r specify Time in 12-hour interval with AM and PM time domain
    %S Specify Seconds
    %T Specify the time in 24-hour format
    %U Specify week and treat Sunday as the first day of the week
    %u Specify week and treat Monday as the first day of the week
    %W Specify weekday name from Sunday to Saturday
    %w Specify Day of the week as 0 = Sunday and 6 = Saturday
    %Y Specify year in 4 digits numeric form such as 1999, 1990, 2020, etc.
    %y Specify year in 2 digits numeric form such as 20, 99, 01, etc.

    11. Day()

    It returns the number of days from the date. Example 

    SELECT DAY('2020-01-01');

Output
+-------------------+
| DAY('2020-01-01') |
+-------------------+
|                 1 |
+-------------------+

    12. DAYNAME()

    It returns the weekday name of the Date. Example 

    SELECT DAYNAME('2020-01-01');

Output
+-----------------------+
| DAYNAME('2020-01-01') |
+-----------------------+
| Wednesday             |
+-----------------------+

    13. DAYOFMONTH()

    It returns the day of the month from the passed date. Example 

    SELECT DAYOFMONTH('2020-01-01');

Output
+--------------------------+
| DAYOFMONTH('2020-01-01') |
+--------------------------+
|                        1 |
+--------------------------+

    14. DAYOFWEEK()

    This function returns the number of weeks from the date. Example 

    SELECT DAYOFWEEK('2020-02-13');

Output
+-------------------------+
| DAYOFWEEK('2020-02-13') |
+-------------------------+
|                       5 |
+-------------------------+

    15. DAYOFYEAR()

    It is similar to the DAY() Function Example 

    SELECT DAYOFYEAR('2020-02-13');

Output
+-------------------------+
| DAYOFYEAR('2020-02-13') |
+-------------------------+
|                      44 |
+-------------------------+

    16. EXTRACT()

    EXTRACT () function is used to extract a specific unit from a DateTime expression. Syntax 

    EXTRACT(unit FROM date)

    Example 

    SELECT EXTRACT(MONTH FROM '2020-01-09');

Output
+----------------------------------+
| EXTRACT(MONTH FROM '2020-01-09') |
+----------------------------------+
|                                1 |
+----------------------------------+

    17. FROM_DAYS()

    This Function considers the Gregorian calendar(1582) as a base and converts the number of days into a Date. Example 

    SELECT FROM_DAYS(737669);

Output
+-------------------+
| FROM_DAYS(737669) |
+-------------------+
| 2019-09-02        |
+-------------------+

    18.  FROM_UNIXTIME()

    This Function accepts a numeric value which represents the number of seconds from the UNIX timestamp. Example 

    SELECT FROM_UNIXTIME(1575996580);

Output
+---------------------------+
| FROM_UNIXTIME(1575996580) |
+---------------------------+
| 2019-12-10 22:19:40       |
+---------------------------+

    19. HOUR()

    This function returns the number of HOURS form the time expression. Example 

    SELECT HOUR('20:10:04');

Output
+------------------+
| HOUR('20:10:04') |
+------------------+
|               20 |
+------------------+

    20. LAST_DAY()

    It returns the last date of the month from the date expression. Example 

    SELECT LAST_DAY('2020-02-01');

Output
+------------------------+
| LAST_DAY('2020-02-01') |
+------------------------+
| 2020-02-29             |
+------------------------+

    21. LOCALTIME & LOCALTIME()

    These functions are similar to NOW() and return the current date and time. Example 

    SELECT LOCALTIME;

Output
+---------------------+
| LOCALTIME           |
+---------------------+
| 2020-05-15 13:32:14 
+---------------------+

    22. MAKEDATE()

    This Function can create a date expression by mentioning the Year and number of days. Syntax 

    MAKEDATE(year, day of the year)

    Example 

    SELECT MAKEDATE(2020,40);

Output
+-------------------+
| MAKEDATE(2020,40) |
+-------------------+
| 2020-02-09        |
+-------------------+

    23. MAKETIME()

    This Function can create time expression by mentioning the Hour, minute and second arguments. Example 

    SELECT MAKETIME(11,10,20);

Output
+--------------------+
| MAKETIME(11,10,20) |
+--------------------+
| 11:10:20           |
+--------------------+

    24. MICROSECOND()

    It can extract the Microseconds from the datetime expression. Example 

    SELECT MICROSECOND('11:00:00.28282');

Output
+-------------------------------+
| MICROSECOND('11:00:00.28282') |
+-------------------------------+
|                        282820 |
+-------------------------------+

    25. Minute()

    It can extract minutes from a datetime expression. Example 

    SELECT MINUTE('2019-05-04 11:03:01');

Output
+-------------------------------+
| MINUTE('2019-05-04 11:03:01') |
+-------------------------------+
|                             3 |
+-------------------------------+

    26. Month()

    It can extract a month from a Date Time expression. Example 

    SELECT MONTH('2019-05-04 11:03:01');

Output
+------------------------------+
| MONTH('2019-05-04 11:03:01') |
+------------------------------+
|                            5 |
+------------------------------+

    27. MONTHNAME()

    It can return the Month name from the datetime expression Example 

    SELECT MONTHNAME('2019-05-04 11:03:01');

Output
+----------------------------------+
| MONTHNAME('2019-05-04 11:03:01') |
+----------------------------------+
| May                              |
+----------------------------------+

    28. PERIOD_ADD(Period, Num)

    This Function adds the number of months Num to a period “Period”. Here the period will be in the form of YYYMM, and it returns the output in the form of YYYYMM. Syntax 

    PERIOD_ADD(YYYYMM, Month)

    Example 

    SELECT PERIOD_ADD(202001,2);

Output
+----------------------+
| PERIOD_ADD(202001,2) |
+----------------------+
|               202003 |
+----------------------+

    29. PERIOD_DIFF()

    This function returns the difference between the two periods. Syntax 

    PERIOD_DIFF(YYYYMM, YYYYMM)

    Example 

    SELECT PERIOD_DIFF(202003,202001);

Output
+----------------------------+
| PERIOD_DIFF(202003,202001) |
+----------------------------+
|                          2 |
+----------------------------+

    30. SECOND()

    It can extract the Seconds from a DateTime expression. Example 

    SELECT SECOND('2019-05-04 11:03:01');

Output
+-------------------------------+
| SECOND('2019-05-04 11:03:01') |
+-------------------------------+
|                             1 |
+-------------------------------+

    31. STR_TO_DATE()

    This function accepts a string and converts it into a DateTime expression using the DateTIme specifiers. Syntax 

    STR_TO_DATE(Date_str,format)

    Example 

    SELECT STR_TO_DATE('01/31/2020', '%m/%d/%Y');

Output
+---------------------------------------+
| STR_TO_DATE('01/31/2020', '%m/%d/%Y') |
+---------------------------------------+
| 2020-01-31                            |
+---------------------------------------+

    32. SYSDATE()

    Similar to NOW() Function it returns the current date present in the system. Example 

    SELECT SYSDATE();

Output
+---------------------+
| SYSDATE()           |
+---------------------+
| 2020-05-15 13:52:44 |
+---------------------+

    33. TIMEDIFF()

    It returns the time difference between two datetime expression. Example 

    SELECT TIMEDIFF('2020-12-31 23:59:59','2019-12-30 23:59:59');

Output
+-----------------------------------------------------------+
| TIMEDIFF('2020-12-31 23:59:59',    '2019-12-30 23:59:59') |
+-----------------------------------------------------------+
| 838:59:59                                                 |
+-----------------------------------------------------------+

    34. TIMESTAMP()

    It returns the UNIX timestamp of the datetime expression. Example 

    SELECT TIMESTAMP('2020-01-01');

Output
+-------------------------+
| TIMESTAMP('2020-01-01') |
+-------------------------+
| 2020-01-01 00:00:00     |
+-------------------------+

    35. Time_FORMAT()

    It is similar to DATE_FORMAT() Function for Time. Example 

    SELECT TIME_FORMAT('23:05:00', '%r');

Output
+-------------------------------+
| TIME_FORMAT('23:05:00', '%r') |
+-------------------------------+
| 11:05:00 PM                   |
+-------------------------------+

    36. TIME_TO_SEC()

    This Function can convert a give time expression to the number of seconds. Example 

    SELECT TIME_TO_SEC('12:00:00');

Output
+-------------------------+
| TIME_TO_SEC('12:00:00') |
+-------------------------+
|                   43200 |
+-------------------------+

    37. UTC_DATE()

    This function returns the current UTC date. Example 

    SELECT UTC_DATE();

Output
+------------+
| UTC_DATE() |
+------------+
| 2020-05-15 |
+------------+

    38. WEEKDAY()

    This Function returns a weekday from a date expression, and the weekday range from 0 to 6, where 0 = Monday and 6 = Sunday. Example 

    SELECT WEEKDAY('2020-11-04');

Output
+-----------------------+
| WEEKDAY('2020-11-04') |
+-----------------------+
|                     2 |
+-----------------------+

    39. YEAR()

    This Function can extract Year from a DateTime expression. Example 

    SELECT YEAR('2020-11-04');

Output
+--------------------+
| YEAR('2020-11-04') |
+--------------------+
|               2020 |
+--------------------+

    40. YEARWEEK():

    It returns the Year and week from a date expression in YYYYMM format. Example 

    SELECT YEARWEEK('2020-11-04');

Output
+------------------------+
| YEARWEEK('2020-11-04') |
+------------------------+
|                 202044 |
+------------------------+

    Summary

    • SQL has a specific data type for Date and Time data.
    • However, date and time mentioned inside single and double inverted comma like a string, still they are different from the string.
    • In SQL, we have different date time functions to perform operations on Date time expression.
    • Most of the datetime functions are similar and perform the same operation.
    • All the functions we have mentioned work fine of MYSQL.
    Previous Tutorial Next Tutorial